import warnings
from datetime import datetime, timedelta

import pandas as pd
from flask import Flask, jsonify, request
from flask_cors import CORS
from flask_sqlalchemy import SQLAlchemy
import jieba
from collections import Counter
from collections import defaultdict
import numpy as np
import matplotlib.pyplot as plt
from sqlalchemy import func, desc
from snownlp import SnowNLP
import statsmodels.api as sm
from scipy import stats
from utils.config import get_config

# 获取数据库配置信息
config = get_config('../server_config.ini', 'postgresql')
# 组装数据库连接
db_url = f"postgresql://{config['user']}:{config['password']}@{config['host']}:{config['port']}/{config['database']}"

app = Flask(__name__)
CORS(app)
app.config['SQLALCHEMY_DATABASE_URI'] = db_url

db = SQLAlchemy(app)


class PhoneParams(db.Model):
    __tablename__ = 'phone_params'

    # 定义表的列
    product_id = db.Column(db.String(50), primary_key=True)  # 主键
    title = db.Column(db.Text, nullable=False)  # 非空文本字段
    product_name = db.Column(db.Text, nullable=False)  # 非空文本字段
    brand = db.Column(db.String(50), nullable=False)  # 非空字符串字段
    cpu = db.Column(db.String(50))  # 字符串字段，可为空
    running_memory = db.Column(db.String(50))  # 字符串字段，可为空
    colour = db.Column(db.String(50))  # 字符串字段，可为空
    th_p_standards = db.Column(db.String(50))  # 字符串字段，可为空
    charge_power = db.Column(db.String(50))  # 字符串字段，可为空
    ipv6 = db.Column(db.Boolean)  # 布尔字段
    screen_material = db.Column(db.String(50))  # 字符串字段，可为空
    camera_pixel = db.Column(db.String(50))  # 字符串字段，可为空
    internal_storage = db.Column(db.String(50))  # 字符串字段，可为空
    screen_resolution = db.Column(db.String(50))  # 字符串字段，可为空
    colour_scheme = db.Column(db.String(50))  # 字符串字段，可为空
    rank = db.Column(db.Integer, nullable=False)  # 新增的 rank 字段，整数类型，不可为空

    def __init__(self, product_id, title, product_name, brand,
                 cpu=None, running_memory=None, colour=None,
                 th_p_standards=None, charge_power=None, ipv6=False,
                 screen_material=None, camera_pixel=None,
                 internal_storage=None, screen_resolution=None,
                 colour_scheme=None, rank=None):
        self.product_id = product_id
        self.title = title
        self.product_name = product_name
        self.brand = brand
        self.cpu = cpu
        self.running_memory = running_memory
        self.colour = colour
        self.th_p_standards = th_p_standards
        self.charge_power = charge_power
        self.ipv6 = ipv6
        self.screen_material = screen_material
        self.camera_pixel = camera_pixel
        self.internal_storage = internal_storage
        self.screen_resolution = screen_resolution
        self.colour_scheme = colour_scheme
        self.rank = rank  # 初始化 rank 字段

    def params_to_dict(self):
        return {
            'product_id': self.product_id,
            'title': self.title,
            'product_name': self.product_name,
            'brand': self.brand,
            'cpu': self.cpu,
            'running_memory': self.running_memory,
            'colour': self.colour,
            'th_p_standards': self.th_p_standards,
            'charge_power': self.charge_power,
            'ipv6': self.ipv6,
            'screen_material': self.screen_material,
            'camera_pixel': self.camera_pixel,
            'internal_storage': self.internal_storage,
            'screen_resolution': self.screen_resolution,
            'colour_scheme': self.colour_scheme,
            'rank': self.rank
        }


class HistoryPrice(db.Model):
    """
    历史价格
    """
    product_id = db.Column(db.String, primary_key=True)
    date_time = db.Column(db.Date, primary_key=True)
    price = db.Column(db.String)
    __table_args__ = (
        db.PrimaryKeyConstraint('product_id', 'date_time'),
    )

    def __init__(self, product_id, date_time, price):
        self.product_id = product_id
        self.date_time = date_time
        self.price = price

    def to_dict(self):
        return {
            'date_time': self.date_time.strftime('%Y-%m-%d'),  # 假设date是datetime类型，需要转换为字符串
            'price': round(self.price, 2)
        }


class Evaluation(db.Model):
    """
    好评、中评、差评率
    """
    product_id = db.Column(db.String, primary_key=True)
    good_rate = db.Column(db.Float)
    medium_rate = db.Column(db.Float)
    bad_rate = db.Column(db.Float)
    __table_args__ = (
        db.PrimaryKeyConstraint('product_id'),
    )

    def __init__(self, product_id, good_rate, medium_rate, bad_rate):
        self.product_id = product_id
        self.good_rate = good_rate
        self.medium_rate = medium_rate
        self.bad_rate = bad_rate

    def to_dict(self):
        return {
            'product_id': self.product_id,
            'good_rate': self.good_rate,
            'medium_rate': self.medium_rate,
            'bad_rate': self.bad_rate
        }


class Comments(db.Model):
    """
    评论
    """
    __tablename__ = 'comments'
    product_id = db.Column(db.String)
    score = db.Column(db.Integer)
    rate = db.Column(db.Integer)
    user_comment = db.Column(db.String)
    comment_id = db.Column(db.String, primary_key=True)

    def __init__(self, product_id, score, rate, user_comment, comment_id):
        self.product_id = product_id
        self.rate = rate
        self.score = score
        self.user_comment = user_comment
        self.comment_id = comment_id


def success(msg, data):
    return {
        'code': 200,
        'message': msg,
        'data': data
    }


def error(msg):
    return {
        'code': 400,
        'message': msg
    }


@app.route('/history_price/<product_id>')
def index(product_id):
    """
    根据产品ID查询历史价格记录，并返回查询结果的JSON格式。

    参数:
    product_id -- 查询的产品ID

    返回:
    JSON格式的历史价格数据，包含操作成功与否的提示和具体数据。
    """
    try:
        # 使用ORM方式执行SQL查询，获取指定产品ID的历史价格记录(升序)
        history_prices = HistoryPrice.query.filter(
            HistoryPrice.product_id == product_id
        ).order_by(HistoryPrice.date_time.asc()).all()

        # 查询最大价格和最小价格
        max_price = db.session.query(func.max(HistoryPrice.price)).filter(
            HistoryPrice.product_id == product_id).scalar()
        min_price = db.session.query(func.min(HistoryPrice.price)).filter(
            HistoryPrice.product_id == product_id).scalar()

        # 将查询到的历史价格对象列表转换为字典列表，方便后续JSON序列化
        history_price_dicts = [data.to_dict() for data in history_prices]

        # 构造返回结果，包含最大价格和最小价格
        result = {
            'history_prices': history_price_dicts,
            'max_price': max_price,
            'min_price': min_price
        }

        # 返回操作成功的结果，包含历史价格数据和最大、最小价格
        return jsonify(success('操作成功', result))
    except Exception as e:
        # 打印异常以便调试
        print(e)
        # 返回操作失败的结果
        return jsonify(error('操作失败'))


@app.route('/search', methods=['POST'])
def search():
    # 获取请求中的 product_name 参数
    product_name = request.json.get('product_name', '')  # 使用 get 方法提供默认值，以防参数不存在
    print(product_name)
    # 使用 LIKE 操作符进行模糊搜索
    # 假设 product_name 参数不为空，并且我们想要搜索 title 和 product_name 字段
    try:
        # 使用 ILIKE 来进行不区分大小写的模糊搜索
        query = PhoneParams.query.filter(
            (PhoneParams.title.ilike(f'%{product_name}%')) |
            (PhoneParams.product_name.ilike(f'%{product_name}%'))
        )
        # 获取查询结果
        results = query.all()

        # 准备返回的数据格式
        search_results = [
            {
                'product_id': result.product_id,
                'title': result.title,
                'product_name': result.product_name,
                'brand': result.brand,
                'rank': result.rank
            }
            for result in results
        ]
        # 返回 JSON 响应
        return jsonify(success('搜索成功', search_results))
    except Exception:
        # 如果 product_name 参数为空，则返回错误信息
        return jsonify(error('请输入搜索关键字'))


@app.route('/rank', methods=['GET'])
def rank():
    try:
        # 使用 all() 获取查询结果，按照 rank 升序排列
        # 这里假设 rank 字段值越小，销量越高

        ranked_phones = PhoneParams.query.order_by(PhoneParams.rank).all()

        # 准备返回的数据格式
        ranked_data = [
            {
                "rank": phone.rank,
                "product_id": phone.product_id,
                "title": phone.title,
                "product_name": phone.product_name,
                "brand": phone.brand,
                # 可以继续添加其他需要返回的字段
            }
            for phone in ranked_phones
        ]
        # 返回 JSON 响应
        return jsonify(success("操作成功", ranked_data))

    except Exception as e:
        return jsonify(error("操作失败"))


@app.route('/phone_info/<product_id>')
def phone_info(product_id):
    """根据产品ID查询商品信息，并返回查询结果的JSON格式。


    参数:
    product_id -- 查询的产品ID

    返回:
    JSON格式的商品信息数据，包含操作成功与否的提示和具体数据。
    """
    try:
        # 根据产品ID查询商品信息，这里假设您有对应的ORM模型，命名为PhoneInfo
        phone_params = PhoneParams.query.filter(PhoneParams.product_id == product_id).first()

        # 如果查询到了商品信息
        if phone_params:
            # 将查询到的商品信息对象转换为字典，方便后续JSON序列化
            phone_params_dict = phone_params.params_to_dict()

            # 返回操作成功的结果，包含商品信息数据
            return jsonify(success('操作成功', phone_params_dict))
        else:
            # 如果未查询到商品信息，返回404错误
            return jsonify(error('未找到对应的商品信息'))
    except Exception as e:
        # 返回操作失败的结果
        return jsonify(error('操作失败'))


@app.route('/brand_count')
def brand_count():
    '''
    返回每个品牌手机的数量
    返回示例：
    :return:{
    "code": 200,
    "data": {
        "Apple": {
            "count": 39
        },
        "荣耀（HONOR）": {
            "count": 14
        }
    },
    "message": "操作成功"
}
    '''
    try:
        ranked_phones = PhoneParams.query.order_by(PhoneParams.rank).all()
        ranked_data = {}
        for phone in ranked_phones:
            brand = phone.brand
            if brand in ranked_data:
                ranked_data[brand]["count"] += 1
            else:
                ranked_data[brand] = {"count": 1}

        return jsonify(success('操作成功', ranked_data))
    except Exception as e:
        # 返回操作失败的结果
        return jsonify(error('操作失败'))


def read_csv(file_path):
    return pd.read_csv(file_path)


def calculate_reviews(reviews_file, details_file):
    try:
        # 读取CSV文件
        reviews_df = read_csv(reviews_file)
        details_df = read_csv(details_file)

        # 合并两个数据集
        merged_df = pd.merge(reviews_df, details_df, left_on='Product ID', right_on='product_id')

        # 按品牌分组并计算好评数、中评数和差评数
        result = merged_df.groupby('brand').agg(
            goodCount=('Positive Reviews', 'sum'),
            middleCount=('Neutral Reviews', 'sum'),
            badCount=('Negative Reviews', 'sum')
        ).reset_index()

        # 转换为所需的字典格式
        data = []
        for index, row in result.iterrows():
            data.append({
                "brand": row['brand'],
                "goodCount": row['goodCount'],
                "middleCount": row['middleCount'],
                "badCount": row['badCount']
            })

        return jsonify(success("操作成功", data))
    except Exception as e:
        return jsonify(error("操作失败"))


@app.route('/get_reviews_summary', methods=['GET'])
def get_reviews_summary():
    """
       返回各品牌好中差评占比
       返回实例：
       return:{
    "code": 200,
    "data": {
        "Apple": {
            "badCount":40
            "goodCount": 39
            "middleCount":20

        },
        "荣耀（HONOR）": {
            "badCount":40
            "goodCount": 14
            "middleCount":20

        }
    },
    "message": "操作成功"
       """
    reviews_file = '../data_csv/comment/reviews.csv'
    details_file = '../data_csv/product_id/jd_product_details.csv'
    return calculate_reviews(reviews_file, details_file)


@app.route('/predict_linear/<product_id>')
def predict_linear(product_id):
    """
    根据产品ID查询历史价格记录，并对后面一周的价格进行预测。

    参数:
    product_id -- 查询的产品ID

    返回:
    JSON格式的预测价格数据(7天)。
    格式示例：
    [{'date': '2024-06-07', 'price': 8133.45},
    {'date': '2024-06-08', 'price': 8125.80}]
    """
    try:
        # 使用ORM方式执行SQL查询，获取指定产品ID的历史价格记录(升序)
        history_prices = HistoryPrice.query.order_by(HistoryPrice.date_time.asc()).filter(
            HistoryPrice.product_id == product_id).all()

        # 将查询到的历史价格对象列表转换为字典列表，方便后续JSON序列化
        history_price_dicts = []
        for data in history_prices:
            history_price_dicts.append(data.to_dict())

        # 提取历史价格
        prices = [float(entry['price']) for entry in history_price_dicts]
        # 将字符串日期转换为日期对象
        dates = [datetime.strptime(entry['date_time'], '%Y-%m-%d').date() for entry in history_price_dicts]
        flag = True
        unique_prices = []
        unique_dates = []
        for i in range(1, len(prices)):
            if prices[i] != prices[0] and flag:
                flag = False
                unique_prices.append(prices[i])
                unique_dates.append(dates[i])
            if not flag:
                unique_prices.append(prices[i])
                unique_dates.append(dates[i])

        # 转换日期为天数，从第一天开始计数
        start_date = min(unique_dates)
        days = [(date - start_date).days for date in unique_dates]

        # 计算线性回归参数
        X = np.array(days).reshape(-1, 1)
        y = np.array(unique_prices)
        A = np.vstack([X.T, np.ones_like(X.T)]).T

        # m, c = np.linalg.lstsq(A, y, rcond=None)[0]的人工实现

        # 计算平均日期和平均价格
        X_mean = sum(days) / len(days)
        y_mean = sum(unique_prices) / len(unique_prices)

        # 计算 m 的值
        numerator = sum((x - X_mean) * (y - y_mean) for x, y in zip(days, unique_prices))
        denominator = sum((x - X_mean) ** 2 for x in days)
        m = numerator / denominator

        # 计算 c 的值
        c = y_mean - m * X_mean

        # 预测下一周的价格
        next_month_prices = []
        next_month_day = max(days) + 7
        for day in range(max(days) + 1, next_month_day + 1):
            # 计算每一天的价格
            predicted_price = round(m * day + c, 2)
            # 构建日期字符串
            next_date = start_date + timedelta(days=day)
            next_date_str = next_date.strftime('%Y-%m-%d')
            # 将日期和价格添加到列表中
            next_month_prices.append({"date_time": next_date_str, "price": predicted_price})

        # 返回操作成功的结果，包含历史价格数据
        return jsonify(success('操作成功', next_month_prices))
    except Exception as e:
        # 返回操作失败的结果
        print(e)
        return jsonify(error('操作失败'))


@app.route('/predict_ARIMA/<product_id>')
def predict_ARIMA(product_id):
    """
    根据产品ID查询历史价格记录，并对后面一个月的价格进行预测。
    PS:当前还不能用

    参数:
    product_id -- 查询的产品ID

    返回:
    JSON格式的预测价格数据(7天)。
    格式示例：
    [{'date': '2024-06-07', 'price': 8133.45},
    {'date': '2024-06-08', 'price': 8125.80}]
    """
    try:
        # 使用ORM方式执行SQL查询，获取指定产品ID的历史价格记录(升序)
        warnings.filterwarnings('ignore')
        plt.style.use('seaborn-v0_8-poster')
        data_from_db = HistoryPrice.query.order_by(HistoryPrice.date_time.asc()).filter(
            HistoryPrice.product_id == product_id).all()
        data_as_dict = [{'date_time': entry.date_time, 'price': entry.price} for entry
                        in data_from_db]
        df = pd.DataFrame(data_as_dict)
        df['price'] = df['price'].astype(float)
        # df['product_id'] = df['product_id'].astype()

        # df = pd.DataFrame.from_records(data_from_db, columns=['product_id','date_time', 'price'])
        # 时间戳转为datetime
        df.date_time = pd.to_datetime(df.date_time, format='%Y-%m-%d %H:%M:%S')
        # 将时间作为索引
        df.index = df.date_time
        # 使用 normalize 方法将索引中的时间部分归零
        df.index = df.index.normalize()
        print(df.date_time.dtypes)
        # 按日聚合，捕获并显示任何异常
        try:
            df = df.resample('D').mean()
        except Exception as e:
            print(f"Resampling error: {e}")

        # Box-Cox 变换，增强数据
        df['price_box'], lmbda = stats.boxcox(df.price)

        def invboxcox(y, lmbda):
            if lmbda == 0:
                return (np.exp(y))
            else:
                return (np.exp(np.log(lmbda * y + 1) / lmbda))

        # Prediction
        df2 = df[['price']]
        # date_list = df.date_time
        last_date = df.index.max()
        date_list = pd.date_range(start=last_date + pd.Timedelta(days=1), periods=7)
        future = pd.DataFrame(index=date_list, columns=df.columns)
        df2 = pd.concat([df2, future])
        model = sm.tsa.statespace.SARIMAX(df.price_box, order=(2, 1, 1), seasonal_order=(0, 1, 0, 15)).fit(disp=-1)
        forecast = model.predict(start=date_list.min(), end=date_list.max())
        # 这个forecast_in是最终的预测数据
        forecast_in = invboxcox(forecast, lmbda)
        df2['forecast'] = invboxcox(model.predict(start=date_list.min(), end=date_list.max()), lmbda)
        last_seven_rows = df2.tail(7)
        result = []

        # 将查询到的历史价格对象列表转换为字典列表，方便后续JSON序列化
        for index, row in last_seven_rows.iterrows():
            date = str(index.date())
            price = round(row['forecast'], 2)
            result.append({'date_time': date, 'price': price})
        # 返回操作成功的结果，包含历史价格数据
        return jsonify(success('操作成功', result))
    except Exception as e:
        # 返回操作失败的结果
        print(e)
        return jsonify(error('操作失败'))


@app.route('/evaluation/<product_id>')
def evaluation(product_id):
    """根据产品ID查询商品好评中评差评率，并返回查询结果的JSON格式。


    参数:
    product_id -- 查询的产品ID

    返回:
    JSON格式的好评中评差评率，包含操作成功与否的提示和具体数据。
    返回示例：
    {
    "code": 200,
    "data": {
        "bad_rate": 0.0229357798165137,
        "good_rate": 0.963302752293578,
        "medium_rate": 0.0137614678899082,
        "product_id": "100082148760"
    },
    "message": "操作成功"
}
    """
    try:
        # 根据产品ID查询商品信息，这里假设您有对应的ORM模型，命名为PhoneInfo
        evaluations = Evaluation.query.filter(Evaluation.product_id == product_id).first()

        # 如果查询到了商品信息
        if evaluations:
            # 将查询到的商品信息对象转换为字典，方便后续JSON序列化
            evaluations_dict = evaluations.to_dict()

            # 返回操作成功的结果，包含商品信息数据
            return jsonify(success('操作成功', evaluations_dict))
        else:
            # 如果未查询到商品信息，返回404错误
            return jsonify(error('未找到对应的评价信息', 404))
    except Exception as e:
        # 返回操作失败的结果
        return jsonify(error('操作失败'))


def load_stopwords(filepath):
    with open(filepath, 'r', encoding='utf-8') as file:
        stopwords = [line.strip() for line in file.readlines()]
    # 直接将列表转换为集合，无需使用 add 方法
    return set(stopwords)


# 过滤掉停用词、所有空白字符、单个词
def filter_words(words, stopwords):
    # 过滤掉停用词、"\r\n" 和单个词
    return [
        word for word in words
        if word.strip('\r\n ') not in stopwords and len(word.strip('\r\n ')) > 1
    ]


# 分词并筛选好评和差评


# 分词并筛选好评和差评
def process_comments(comments, stopwords):
    # 使用 defaultdict 来存储词频统计
    word_counts = defaultdict(int)

    for comment in comments:
        seg_list = jieba.cut(comment.user_comment)
        # 过滤词
        filtered_words = filter_words(seg_list, stopwords)
        for word in filtered_words:
            word_counts[word] += 1

    return word_counts


@app.route('/word/<product_id>', methods=['GET'])
def review(product_id):
    try:
        # 查询 product_id 的所有评论
        goodcomments = Comments.query.filter_by(product_id=product_id, rate=3).all()
        # 筛选出rate为1的评论
        bad_comments_rate_1 = Comments.query.filter_by(product_id=product_id, rate=1).all()
        # 筛选出rate为2的评论
        bad_comments_rate_2 = Comments.query.filter_by(product_id=product_id, rate=2).all()
        # 合并两个列表
        badComments = bad_comments_rate_1 + bad_comments_rate_2
        # 加载停用词
        stopwords = load_stopwords('tokens/StopwordsCN.txt')
        # 分词并筛选好评和差评
        good_comments = process_comments(goodcomments, stopwords)
        bad_comments = process_comments(badComments, stopwords)
        # 统计词频
        good_word_counts = Counter(good_comments)
        bad_word_counts = Counter(bad_comments)

        # 定义文件路径
        good_file_path = 'tokens/goodComment.txt'
        bad_file_path = 'tokens/badComment.txt'

        # 写入分词结果到文件
        with open(good_file_path, 'w', encoding='utf-8') as file:
            for word in good_comments:
                file.write(word + '\n')

        with open(bad_file_path, 'w', encoding='utf-8') as file:
            for word in bad_comments:
                file.write(word + '\n')

            # 构造响应数据
            response_data = {
                "good": [
                    {"word": word, "value": count} for word, count in good_word_counts.most_common(30)
                ],
                "bad": [
                    {"word": word, "value": count} for word, count in bad_word_counts.most_common(50)
                ]
            }

            return jsonify(success("操作成功", response_data))
    except Exception as e:
        # 如果发生错误，返回错误信息
        return jsonify(error("操作失败"))


@app.route('/priceDistribution', methods=['GET'])
def priceDistribution():
    try:
        # 查询所有手机，按 rank 排序
        phones = PhoneParams.query.order_by(PhoneParams.rank).all()

        # 准备返回的数据结构
        price_distribution = []

        for phone in phones:
            # 对于每个手机，查询最新的价格记录
            latest_price = HistoryPrice.query.with_entities(HistoryPrice.price).filter_by(
                product_id=phone.product_id).order_by(desc(HistoryPrice.date_time)).first()

            # 将手机信息和最新价格整合
            price_distribution.append({
                "price": str(latest_price.price) if latest_price else "No price data",
                "brand": phone.brand if hasattr(phone, 'brand') else "Brand data missing",
                "product_id": phone.product_id,
                "rank": str(phone.rank)
            })

        # 返回 JSON 响应
        return jsonify(success("操作成功", price_distribution))

    except Exception as e:
        # 如果发生错误，返回错误信息
        return jsonify(error("操作失败"))


@app.route('/comment/<product_id>', methods=['GET'])
def comment(product_id):
    try:
        # 使用随机排序并限制结果为20条
        random_comments = Comments.query \
            .filter(Comments.product_id == product_id) \
            .order_by(func.random()).limit(20).all()
        # comments  = Comments.query.filter(Comments.product_id == product_id).all()
        # 将查询结果转换为字典列表
        comments_data = [{
            "user_comment": comment.user_comment,
            "emotion": SnowNLP(comment.user_comment).sentiments,
            "product_id": comment.product_id,
            "score": comment.score,
            "rate": comment.rate
        } for comment in random_comments]

        # 返回JSON响应
        return jsonify(success("操作成功", comments_data))
    except Exception as e:
        # 如果发生错误，返回错误信息
        return jsonify(error("操作失败"))


# 接收用户反馈
@app.route('/feedback', methods=['POST'])
def feedback():
    try:
        data = request.get_json()
        print(data)
        print(f'Feedback: {data["message"]} from user: {data["name"]},email: {data["email"]}')
        return jsonify(success("操作成功", None))
    except Exception as e:
        print(f'Error: {e}')
        return jsonify(error("操作失败"))


if __name__ == '__main__':
    app.debug = True
    app.run()
